﻿using Helper.Models;
using System;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Windows;

namespace Helper
{
    public class DbUtilities
    {
      /*  public static bool CreateNewDbUser(DbUserInfo dbUsr, string pwd)
        {
            bool succeeded = true;
            succeeded = succeeded && DbUtilities.SaveDBUser(dbUsr);
            succeeded = succeeded && DbUtilities.CreateInstanceLogin(dbUsr, pwd);
            succeeded = succeeded && DbUtilities.CreateDBUser(dbUsr);
            succeeded = succeeded && DbUtilities.GrantPermissions(dbUsr);
            return succeeded;
        }

        private static bool GrantPermissions(DbUserInfo tempUsr)
        {
            bool mres = true;
            if (tempUsr.AccessPermission >= ObjectAccessPermission.User)
            {
                string insertStr = "GRANT SELECT ON SCHEMA::[Users] TO " + tempUsr.UserName;
                mres = mres && DataAccessHelper.ExecuteNonQuery(insertStr);
                insertStr = "GRANT UPDATE ON SCHEMA::[Users] TO " + tempUsr.UserName;
                mres = mres && DataAccessHelper.ExecuteNonQuery(insertStr);
                insertStr = "GRANT DELETE ON SCHEMA::[Users] TO " + tempUsr.UserName;
                mres = mres && DataAccessHelper.ExecuteNonQuery(insertStr);
                insertStr = "GRANT INSERT ON SCHEMA::[Users] TO " + tempUsr.UserName;
                mres = mres && DataAccessHelper.ExecuteNonQuery(insertStr);
            }
            if (tempUsr.AccessPermission >= ObjectAccessPermission.Administrator)
            {
                string insertStr = "GRANT INSERT ON SCHEMA::[Administrators] TO " + tempUsr.UserName;
                mres = mres && DataAccessHelper.ExecuteNonQuery(insertStr);
                insertStr = "GRANT SELECT ON SCHEMA::[Administrators] TO " + tempUsr.UserName;
                mres = mres && DataAccessHelper.ExecuteNonQuery(insertStr);
                insertStr = "GRANT UPDATE ON SCHEMA::[Administrators] TO " + tempUsr.UserName;
                mres = mres && DataAccessHelper.ExecuteNonQuery(insertStr);
                insertStr = "GRANT DELETE ON SCHEMA::[Administrators] TO " + tempUsr.UserName;
                mres = mres && DataAccessHelper.ExecuteNonQuery(insertStr);
            }
            return mres;
        }

        public static bool RemoveUser(DbUserInfo dbUsr)
        {
            bool succeeded = true;
            string delStr =
                "DELETE FROM [User].[User] WHERE UserID ='" + dbUsr.UserID + "'";
            succeeded = succeeded && DataAccessHelper.ExecuteNonQuery(delStr);
            delStr = "DROP USER " + dbUsr.UserName;
            succeeded = succeeded && DataAccessHelper.ExecuteNonQuery(delStr);
            delStr = "USE master\r\n" + "DROP LOGIN " + dbUsr.UserName;
            succeeded = succeeded && DataAccessHelper.ExecuteNonQuery(delStr);
            return succeeded;
        }

        private static bool CreateInstanceLogin(DbUserInfo newUser, string pwd)
        {
            string newLoginStr ="USE master\r\n"+ "CREATE LOGIN " + newUser.UserName + " WITH PASSWORD=N'" + pwd +
                "', DEFAULT_DATABASE=[Starehe], DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF";
            
            bool succeeded = DataAccessHelper.ExecuteNonQuery(newLoginStr,false);
            
            return succeeded;
        }

        private static bool CreateDBUser(DbUserInfo newUser)
        {
            string newUserInsertStr = "";
            if (newUser.AccessPermission == ObjectAccessPermission.Administrator)
                newUserInsertStr = "CREATE USER " + newUser.UserName + " FOR LOGIN " + newUser.UserName +
                   " WITH DEFAULT_SCHEMA=Administrators";
            if (newUser.AccessPermission == ObjectAccessPermission.User)
                newUserInsertStr = "CREATE USER " + newUser.UserName + " FOR LOGIN " + newUser.UserName +
                   " WITH DEFAULT_SCHEMA=Users";
            return DataAccessHelper.ExecuteNonQuery(newUserInsertStr);
        }

        private static bool SaveDBUser(DbUserInfo newUser)
        {
            string INSERTSTR =
           "INSERT INTO [School].[User] (UserId,UserName,PermissionAccess,HashId) " +
           "VALUES(?,?,?,?)";

            ObservableCollection<OleDbParameter> paramColl = new ObservableCollection<OleDbParameter>();
            paramColl.Add(new OleDbParameter("@tpUserId", newUser.UserID));
            paramColl.Add(new OleDbParameter("@tpUserName", newUser.UserName));
            paramColl.Add(new OleDbParameter("@tpPermissionAccess", newUser.AccessPermission.ToString()));
            paramColl.Add(new OleDbParameter("@tpHahId", newUser.PWDCrypt));

            return DataAccessHelper.ExecuteNonQueryWithParameters(INSERTSTR, paramColl);
        }
        */
        public static bool ValidateWindowsUser()
        {
            bool succ = DataAccessHelper.ExecuteNonQuery("");
            return succ;
        }
    }
}
